package com.wiserp.rest.helper;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.wiserp.rest.model.WhLocProd;

public class WhLocProdHelper extends JdbcDaoSupport {
	public List<WhLocProd> findStockByWhLoc(String whLocNo) {
		return this
				.getJdbcTemplate()
				.query("select c.haa012,a.hcc004,a.hcc003,b.bea003,a.hcc019,SUM(a.hcc010) qty from shc03 a left join saf01 b on(a.hcc003=b.bea001) left join sha01 c on(a.hcc004=c.haa001) where a.hcc004=? group by c.haa012,a.hcc004,a.hcc003,b.bea003,a.hcc019",
						new RowMapper<WhLocProd>() {
							public WhLocProd mapRow(ResultSet rs, int i)
									throws SQLException {
								WhLocProd data = new WhLocProd();
								data.setWhNo(rs.getString("haa012"));
								data.setWhLocNo(rs.getString("hcc004"));
								data.setProdNo(rs.getString("hcc003"));
								data.setProdName(rs.getString("bea003"));
								data.setBatchNo(rs.getString("hcc019"));
								data.setQty(rs.getDouble("qty"));
								return data;
							}
						},whLocNo);
	}
	
	public List<WhLocProd> findStockByProdNoAndBatchNo(String prodNo,String batchNo) {
		return this
				.getJdbcTemplate()
				.query("select c.haa012,a.hcc004,a.hcc003,b.bea003,a.hcc019,SUM(a.hcc010) qty from shc03 a left join saf01 b on(a.hcc003=b.bea001) left join sha01 c on(a.hcc004=c.haa001) where a.hcc003=? AND a.hcc019=? group by c.haa012,a.hcc004,a.hcc003,b.bea003,a.hcc019",
						new RowMapper<WhLocProd>() {
							public WhLocProd mapRow(ResultSet rs, int i)
									throws SQLException {
								WhLocProd data = new WhLocProd();
								data.setWhNo(rs.getString("haa012"));
								data.setWhLocNo(rs.getString("hcc004"));
								data.setProdNo(rs.getString("hcc003"));
								data.setProdName(rs.getString("bea003"));
								data.setBatchNo(rs.getString("hcc019"));
								data.setQty(rs.getDouble("qty"));
								return data;
							}
						},prodNo,batchNo);
	}	
	
	public List<WhLocProd> findStockByProdNo(String prodNo) {
		return this
				.getJdbcTemplate()
				.query("select c.haa012,a.hcc004,a.hcc003,b.bea003,a.hcc019,SUM(a.hcc010) qty from shc03 a left join saf01 b on(a.hcc003=b.bea001) left join sha01 c on(a.hcc004=c.haa001) where a.hcc003=? group by c.haa012,a.hcc004,a.hcc003,b.bea003,a.hcc019",
						new RowMapper<WhLocProd>() {
							public WhLocProd mapRow(ResultSet rs, int i)
									throws SQLException {
								WhLocProd data = new WhLocProd();
								data.setWhNo(rs.getString("haa012"));
								data.setWhLocNo(rs.getString("hcc004"));
								data.setProdNo(rs.getString("hcc003"));
								data.setProdName(rs.getString("bea003"));
								data.setBatchNo(rs.getString("hcc019"));
								data.setQty(rs.getDouble("qty"));
								return data;
							}
						},prodNo);
	}	
}
